-- 1. Wybierz wszystkich klientow
SELECT * FROM Customers;

-- 2. Wybierz wszystkie sneakers marki Nike
SELECT * FROM Sneakers
WHERE brand = 'Nike';

-- 3. Wyswietl imie i nazwisko klienta oraz nazwe sneakers, ktore zamowil
SELECT 
    Customers.first_name || ' ' || Customers.last_name AS Klient,
    Sneakers.sneaker_name AS Sneakers
FROM 
    Orders
JOIN 
    Customers ON Orders.customer_id = Customers.customer_id
JOIN 
    Sneakers ON Orders.sneaker_id = Sneakers.sneaker_id;

-- 4. Policz laczna ilosc zamowien dla kazdego modelu sneakers
SELECT 
    sneaker_name,
    SUM(quantity) AS Lacznosc_zamowien
FROM 
    Orders
JOIN 
    Sneakers ON Orders.sneaker_id = Sneakers.sneaker_id
GROUP BY 
    sneaker_name;

-- 5. Wybierz zamowienia z ostatniego miesiaca
SELECT * FROM Orders
WHERE order_date >= date('now', '-1 month');

-- 6. Posortuj sneakers wedlug ceny od najdrozszych do najtanszych
SELECT * FROM Sneakers
ORDER BY price DESC;

-- 7. Wybierz wszystkich klientow, ktorzy zamowili sneakers za wiecej niz 100 dolarow
SELECT 
    Customers.first_name || ' ' || Customers.last_name AS Klient,
    Orders.sneaker_id,
    Orders.price
FROM 
    Orders
JOIN 
    Customers ON Orders.customer_id = Customers.customer_id
WHERE 
    Orders.price > 100;

-- 8. Wyswietl srednia cena sneakers dla kazdego brandu
SELECT 
    brand,
    AVG(price) AS Srednia_cena
FROM 
    Sneakers
GROUP BY 
    brand;

-- 9. Wybierz klientow posortowanych wedlug ilosci zamowien malejaco
SELECT 
    Customers.first_name || ' ' || Customers.last_name AS Klient,
    COUNT(Orders.order_id) AS Ilosc_zamowien
FROM 
    Customers
JOIN 
    Orders ON Customers.customer_id = Orders.customer_id
GROUP BY 
    Customers.customer_id
ORDER BY 
    Ilosc_zamowien DESC;

-- 10. Wyswietl wszystkie zamowienia z data przed 2024-01-01
SELECT * FROM Orders
WHERE order_date < '2024-01-01';